-- @owner: xuwenfei1
-- @date: 2025/3/31
-- @testpoint: gms_utility.expand_sql_text函数查询语句为层次查询语句

@conn PrimaryDbAmode;
--step0：加载插件;expect: 成功
drop extension if exists gms_utility;
create extension  gms_utility;
drop extension if exists gms_xmlgen;
create extension gms_xmlgen;
drop table if exists t_expand0010;

--step1：创建表和视图，其中视图2依赖表1和表2使用union，视图3依赖视图1和视图2，视图4依赖表1和视图2; expect: 创建成功
create table t_expand0010 (
    "integer" integer,
    "float" float,
    "numeric" numeric(20, 6),
    "boolean" boolean,
    "char" char(20),
    "varchar" varchar(20),
    "text" text,
    "blob" blob,
    "raw" raw,
    "date" date,
    "time" time,
    "timestamp" timestamp,
    "json" json,
    "varchar_array" varchar(20)[],
    "money" money,
    "bit" bit(3)
);
insert into t_expand0010
values(
    1,
    1.23456,
    1.234567,
    true,
    '"''<>&char test',
    'varchar"''<>&test',
    'text test"''<>&',
    'ff',
    hextoraw('ABCD'),
    '2024-01-02',
    '18:01:02',
    '2024-02-03 19:03:04',
    '{"a" : 1, "b" : 2}',
    array['abc', '"''<>&', '你好'],
    '12.34'::float8::numeric::money,
    B'101'
 ),
 (
    2,
    2.23456,
    2.234567,
    false,
    '2"''<>&char test',
    '2varchar"''<>&test',
    '2text test"''<>&',
    'eeee',
    hextoraw('ffff'),
    '2026-03-04',
    '20:12:13',
    '2026-05-06 21:13:00',
    '[9,8,7,6]',
    array['&^%@', '"''<>&', '<&y''">'],
    '25.59',
    B'101'
 ),
 (
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null
 );

--step2：执行函数;expect: 成功
declare
    input_sql_text1 clob := 'SELECT "integer", xmltype(gms_xmlgen.getxml(''select * from public.t_expand0010''))
        FROM public.t_expand0010
        START WITH "integer" = 1 OR "integer" = 2
        CONNECT BY nocycle "integer" = PRIOR "integer"';
    output_sql_text1 clob;
begin
    gms_utility.expand_sql_text(input_sql_text1, output_sql_text1);
    raise info 'INFO:output_sql_text1: %', output_sql_text1;
END;
/

--step3：清理环境;expect: 成功
drop extension if exists gms_utility;
drop extension if exists gms_xmlgen;
drop table if exists t_expand0010;

